﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using System.Configuration;
using System.Windows.Forms;
using System.Data.SQLite;

namespace DialogsEditor.DAO
{
    class UtilsDAO
    {

        public static void SaveDialog(DialogEntity d)
        {
            using (SQLiteConnection conn = ConnFactory.getInstancia().GetConnDialog())
            {
                try
                {
                    //string selectTriger = "SELECT id from dialogtrigger WHERE";
                    String sql = "INSERT INTO dialog (title, charac, trig, text, isproject, idparent) VALUES (@title, @charac, @trig, @text, @isproject, @idparent);";
                    SQLiteCommand cmd = new SQLiteCommand(sql, conn);

                    //int idTrig = UtilsDAO.GetId(conn, selectTriger, "nameTrigger", d.trigger.Text);
                    //if (idTrig < 0) throw new IndexOutOfRangeException();

                    cmd.Parameters.AddWithValue("@title", d.title.Text);
                    cmd.Parameters.AddWithValue("@charac", d.charac.Text);
                    cmd.Parameters.AddWithValue("@trig", d.trigger.Text);
                    cmd.Parameters.AddWithValue("@text", d.text.Text);
                    cmd.Parameters.AddWithValue("@isproject", d.IsProject);
                    cmd.Parameters.AddWithValue("@idparent", d.idProject);

                    conn.Open();
                    cmd.ExecuteNonQuery();

                    NewDialogForm.anyNewDialog = true;
                }
                catch (NullReferenceException) { MessageBox.Show("Erro de conexão"); }
                catch (DuplicateNameException) { MessageBox.Show("Já existe um título igual"); }
                catch (IndexOutOfRangeException) { MessageBox.Show("Trigger inválida"); }
                catch (Exception ex)  { MessageBox.Show("Erro na conexao: " + ex.Message, "Error"); }
                finally
                {
                    conn.Close();
                }

            }
        }

        public static void UpdateDialog(DialogEntity d)
        {
            using (SQLiteConnection conn = ConnFactory.getInstancia().GetConnDialog())
            {
                try
                {
                    if (d.idTrig < 0) throw new IndexOutOfRangeException();

                    String sql = "Update dialog set title = @title, charac = @charac, trig = @trig, text = @text, idnextdialog = @idnextdialog, idparent = @idparent WHERE id = @id";
                    SQLiteCommand cmd = new SQLiteCommand(sql, conn);

                    cmd.Parameters.AddWithValue("@title", d.title.Text);
                    cmd.Parameters.AddWithValue("@charac", d.charac.Text);
                    cmd.Parameters.AddWithValue("@trig", d.trigger.Text);
                    cmd.Parameters.AddWithValue("@text", d.text.Text);
                    cmd.Parameters.AddWithValue("@idnextdialog", (d.idNextDialog > 0) ? d.idNextDialog : 0);
                    cmd.Parameters.AddWithValue("@id", d.GetID());
                    cmd.Parameters.AddWithValue("@idparent", d.idProject);

                    conn.Open();
                    cmd.ExecuteNonQuery();

                    NewDialogForm.anyNewDialog = true;
                }
                catch (NullReferenceException) { MessageBox.Show("Erro de conexão"); }
                catch (DuplicateNameException) { MessageBox.Show("Já existe um título igual"); }
                catch (IndexOutOfRangeException) { MessageBox.Show("Trigger inválida"); }
                catch (Exception ex) { MessageBox.Show("Erro na conexao: " + ex.Message, "Error"); }
                finally
                {
                    conn.Close();
                }

            }
        }

        public static void DeleteDialog(DialogEntity d)
        {
            using (SQLiteConnection conn = ConnFactory.getInstancia().GetConnDialog())
            {
                try
                {
                    String sql = "DELETE from dialog WHERE id = @id";
                    SQLiteCommand cmd = new SQLiteCommand(sql, conn);

                    cmd.Parameters.AddWithValue("@id", d.GetID());

                    conn.Open();
                    cmd.ExecuteNonQuery();

                    NewDialogForm.anyNewDialog = true;
                }
                catch (NullReferenceException)
                {
                    MessageBox.Show("Erro de conexão");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Erro na conexao: " + ex.Message, "Error");
                }
                finally
                {
                    conn.Close();
                }

            }
        }

        public static void DeleteProjectDialog(DialogEntity d)
        {
            using (SQLiteConnection conn = ConnFactory.getInstancia().GetConnDialog())
            {
                try
                {
                    String sql = "DELETE from dialog WHERE idparent = @idparent";
                    SQLiteCommand cmd = new SQLiteCommand(sql, conn);

                    cmd.Parameters.AddWithValue("@idparent", d.GetID());

                    conn.Open();
                    cmd.ExecuteNonQuery();

                    NewDialogForm.anyNewDialog = true;
                }
                catch (NullReferenceException)
                {
                    MessageBox.Show("Erro de conexão");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Erro na conexao: " + ex.Message, "Error");
                }
                finally
                {
                    conn.Close();
                }

            }
        }

        public static List<DialogEntity> LoadDialogs(DialogEntity currProj)
        {
            List<DialogEntity> list = new List<DialogEntity>();
            using (SQLiteConnection conn = ConnFactory.getInstancia().GetConnDialog())
            {
                SQLiteDataReader reader = null;
                try
                {
                    conn.Open();
                    string select = "";
                    //if (getProjects)
                    //    select = "select id, title, idtrig, text, idnextdialog from dialog where isproject = 1";
                    //else
                    select = "select id, title, charac, trig, text, idnextdialog, isproject, idparent from dialog where idparent = '" + currProj.GetID() + "' and not isproject";

                    SQLiteCommand cmd = new SQLiteCommand(select, conn);

                    reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        DialogEntity d = null;
                        //if (getProjects)
                        //    d = new DialogEntity(reader.GetInt32("id"), reader.GetString("title"), reader.GetInt32("idtrig"), reader.GetString("text"), reader.GetInt32("idnextdialog"));
                        //else
                        d = new DialogEntity(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetInt32(5), reader.GetBoolean(6), reader.GetInt32(7), list);
                        
                        list.Add(d);
                    }
                }
                catch (NullReferenceException)
                {
                    MessageBox.Show("Erro de conexão");
                }
                catch (SQLiteException mysqlError)
                {
                    MessageBox.Show("Erro de acesso ao BD: " + mysqlError.Message, "Error");
                }
                finally
                {
                    if (reader != null) reader.Close();
                    conn.Close();
                }

            }
            return list;
        }


        /// <summary>
        /// Gets the id of the entity in the select sql
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql">Need to be like as: "SELECT id from TABLE WHERE" </param>
        /// <param name="condition"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int GetId(SQLiteConnection conn, string sql, string condition, string value)
        {
            int result = -1;
            SQLiteDataReader reader = null;
            try
            {
                conn.Open();
                sql += " " + condition + " = ";
                sql += " '" + value + "';";
                SQLiteCommand cmd = new SQLiteCommand(sql, conn);

                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    result = reader.GetInt32(0);
                }
            }
            catch (NullReferenceException)
            {
                MessageBox.Show("Erro de conexão");
            }
            catch (SQLiteException mysqlError)
            {
                MessageBox.Show("Erro de acesso ao BD: " + mysqlError.Message, "Error");
            }
            finally
            {
                if (reader != null) reader.Close();
                conn.Close();
            }

            return result;
        }

        /*public static string GetNameTrigger(int id)
        {
            using (SQLiteConnection conn = ConnFactory.getInstancia().getConexao())
            {
                string result = null;
                MySqlDataReader reader = null;
                try
                {
                    conn.Open();
                    string sql = "select nameTrigger from dialogtrigger WHERE id = '" + id + "';";
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    MySqlDataAdapter data = new MySqlDataAdapter(cmd);

                    reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        result = reader.GetString("nameTrigger");
                    }
                }
                catch (NullReferenceException)
                {
                    MessageBox.Show("Erro de conexão");
                }
                catch (MySqlException mysqlError)
                {
                    MessageBox.Show("Erro de acesso ao BD: " + mysqlError.Message, "Error");
                }
                finally
                {
                    if (reader != null) reader.Close();
                    conn.Close();
                }

                return result;
            }
        }*/

    }
}
